package com.fy.tt.dao.impl;

import com.fy.toolhelper.db.BaseDaoImpl;
import com.fy.tt.dao.EnvironmentDao;
import com.fy.tt.entity.Environment;
import com.fy.tt.entity.query.EnvironmentQuery;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class EnvironmentDaoImpl extends BaseDaoImpl<Environment> implements EnvironmentDao {

    @Override
    public List<Environment> getAllEnvironments(Connection connection) throws SQLException {
        String sql = "select * from bls_environment";
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        ArrayList<Environment> environments = new ArrayList<>();
        while(rs.next()){
            Environment environment = new Environment(rs);
            environments.add(environment);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return environments;
    }

    @Override
    public int addEnvironment(Connection connection, Environment environment) throws SQLException{
        String sql = "insert into bls_environment(id,type_id,content,type) values(?,?,?,?)";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setLong(1,environment.getId());
        pstm.setLong(2,environment.getTypeId());
        pstm.setString(3,environment.getContent());
        pstm.setLong(4,environment.getType());
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }

    @Override
    public int deleteEnvironment(Connection connection, int id) throws SQLException {
        String sql = "delete from bls_environment where id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setLong(1, id);
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }

    @Override
    public int updateEnvironment(Connection connection, Environment environment) throws SQLException {
        String sql = "update bls_environment set content = ? where id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setString(1,environment.getContent());
        pstm.setLong(2,environment.getId());
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }

    @Override
    public List<Environment> getPageEnvironments(Connection connection, int pageSize, int current) throws SQLException{
        int start = pageSize * (current - 1);
        String sql = "select * from bls_environment limit ?,?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setLong(1, start);
        pstm.setLong(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<Environment> environments = new ArrayList<>();
        while(rs.next()){
            Environment environment = new Environment(rs);
            environments.add(environment);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return environments;
    }

    @Override
    public List<Environment> getConditionPageEnvironments(Connection connection, EnvironmentQuery environmentQuery, int pageSize, int current) throws SQLException{
        int start = pageSize * (current - 1);
        String sql = "select * from bls_environment where 1 = 1";

        if(!StringUtils.isEmpty(environmentQuery.getType())){
            sql+=" and typeId =" + environmentQuery.getType();
        }

        if(!StringUtils.isEmpty(environmentQuery.getContent())){
            sql+=" and content like '%" + environmentQuery.getContent() + "%'";
        }


        if(StringUtils.isEmpty(environmentQuery.getType())
                && StringUtils.isEmpty(environmentQuery.getContent())){
            sql = "select * from bls_environment limit ? , ? ";
        }else{
            sql += " limit ? , ?";
        }

        System.out.println("sql = " + sql);

        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setLong(1, start);
        pstm.setLong(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<Environment> environments = new ArrayList<>();
        while(rs.next()){
            Environment environment = new Environment(rs);
            environments.add(environment);
        }
        System.out.println(environments);
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return environments;
    }


    public EnvironmentDaoImpl() throws Exception {
        super();
    }
}